import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;


/**
 * Db.java
 * @author abrahamsantini
 * @version 05-23-2013
 */
public class Db{

	/**
	 * Static instance of Db class.
	 */
	private static Db _db = null;

	public static String userName = "";
	public static String pwd = "";
	public static String dbServerName = "";
	public static String database = "";

	/**
	 * Get the static instance of the Db class.
	 * @return _db The database
	 */
	public static Db getDb() {
		if (_db == null) {
			_db = new Db();
		}

		return _db;
	}

	public boolean setDB(String username, String password, String server, String db) {
		boolean success = true;
		Connection conn = null;
		userName = username;
		pwd = password;
		dbServerName = server;
		database = db;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
		} catch (ClassNotFoundException e) {
			success = false;
			e.printStackTrace();
		} catch (SQLException e) {
			success = false;
			e.printStackTrace();
		}
		return success;
	}

	/**
	 * Enter the vendor's daily sales.
	 * 
	 * @param the_vID The vendor id
	 * @param the_plants The amount of plants sold.
	 * @param the_produce The amount of produce sold
	 * @param the_processor The amount of processed goods sold.
	 * @param the_prep_food The amount of prepared food sold.
	 * @param the_crafter The amount of crafts sold.
	 * @param the_reseller The amount of retail goods sold.
	 * @param the_paid Whether or not the vendor paid the amount due.
	 * @param the_date The date of the transaction
	 * @param the_stalls The number of stalls that the vendor used on that day.
	 * @param the_marketID The id of the market.
	 * @return The  Transaction number that is created.
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public String enterVendorDailySales(String the_vID, String the_plants, String the_produce,
			String the_processor, String the_prep_food, String the_crafter, String the_reseller,
			String the_paid, String the_date, String the_stalls, String the_marketID) throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		String ret = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);

			//inserts new tuple into sales
			statement = conn.prepareStatement("INSERT INTO sale (sPlants, sProduce, sProcessor," +
					"sPreparedFood, sCrafter, sReseller," +
					"saleDate, sNumOfStalls) VALUES (?,?, ?,?, ?,?, ?,?)");
			statement.setDouble(1, Double.parseDouble(the_plants));
			statement.setDouble(2, Double.parseDouble(the_produce));
			statement.setDouble(3, Double.parseDouble(the_processor));
			statement.setDouble(4, Double.parseDouble(the_prep_food));
			statement.setDouble(5, Double.parseDouble(the_crafter));
			statement.setDouble(6, Double.parseDouble(the_reseller));
			statement.setString(7, the_date);
			statement.setInt(8, Integer.parseInt(the_stalls));
			statement.executeUpdate();

			// get saleID
			statement = conn.prepareStatement("SELECT last_insert_id()");
			rs = statement.executeQuery();
			rs.next();
			String saleID = rs.getString(1);

			System.out.println("Sale id = " + saleID);

			statement = conn.prepareStatement("INSERT INTO transaction (vendorID, saleID, " +
					"marketID, tPaid) VALUES (?,?,?,?)");
			statement.setString(1, the_vID);
			statement.setString(2, saleID);
			statement.setString(3, the_marketID);
			statement.setString(4, the_paid);
			statement.executeUpdate();

			// get transaction number
			statement = conn.prepareStatement("SELECT last_insert_id()");
			rs = statement.executeQuery();
			rs.next();
			ret = rs.getString(1);
			System.out.println("Transaction ID = " + ret);
			return ret;


		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}

	public String[] getVendorSales(String the_ID, String the_date) throws SQLException, ClassNotFoundException{
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		String[] ret = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);

			statement = conn.prepareStatement("SELECT sale.sPlants, sale.sProduce, " +
					"sale.sProcessor, sale.sPreparedFood, sale.sCrafter, sale.sReseller," +
					"sale.sNumOfStalls" +
					" FROM transaction, sale WHERE " +
					"transaction.vendorID = ? AND sale.saleID = transaction.saleID AND " +
					"sale.saleDate = ? ");
			statement.setString(1, the_ID);
			statement.setString(2, the_date);
			rs = statement.executeQuery();
			if(rs.next()){
				ResultSetMetaData meta = rs.getMetaData();
				ret = new String[meta.getColumnCount()];
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					ret[i-1] = rs.getString(i);
				}
			}
			return ret;
		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}







	}

	/**
	 * The viewVendor method is used to query master data for a specific vendor.
	 * 
	 * @return The string containing the results of the query
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public String[] viewVendor(final String the_vID) throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		String[] ret = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT * FROM farmersmarketdb.vendor " +  
					"WHERE vendor.vID = ?");
			statement.setString(1, the_vID);
			rs = statement.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			ret = new String[meta.getColumnCount()];
			rs.next();
			for(int i = 1; i <= meta.getColumnCount(); i++ ) {
				ret[i-1] = rs.getString(i);
			}

			return ret;
		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * The updateVendor method is used to update the master data of a
	 * specific vendor.
	 * 
	 * @param the_vID The vendor id of the vendor to be updated.
	 * @param the_fname The first name of the vendor.
	 * @param the_lname The last name of the vendor.
	 * @param the_address The address of the vendor.
	 * @param the_phonenumber The phone number of the vendor.
	 * @param the_email The email of the vendor.
	 * @param the_mem_paid The membership status of the vendor.
	 * @param the_UBI The UBI of the vendor.
	 * @param the_bus_name The business name of the vendor.
	 * @param the_stallNum The stall number of the vendor.
	 * 
	 * @return The string containing the results of the query
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public void updateVendor(final String the_vID, final String the_fname, 
			final String the_lname, final String the_address, 
			final String the_phonenumber, final String the_email,
			final String the_mem_paid, final String the_UBI, final String the_bus_name,
			final String the_stallNum) 
					throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("UPDATE vendor SET vFirstName=?, " +
					"vLastName=?, vAddress=?, vPhoneNumber=?, vEmail=?," +
					"vMem_paid=?, vUBI=?, vBusinessName =?, sStallNumber=? WHERE vID=?");
			statement.setString(1, the_fname);
			statement.setString(2, the_lname);
			statement.setString(3, the_address);
			statement.setString(4, the_phonenumber);
			statement.setString(5, the_email);
			statement.setString(6, the_mem_paid);
			statement.setString(7, the_UBI);
			statement.setString(8,  the_bus_name);
			statement.setString(9, the_stallNum);
			statement.setString(10, the_vID);
			statement.executeUpdate();

		}
		finally {

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}


	/**
	 * The createVendor method inserts a new vendor into the database.
	 * 
	 * @param the_fname The first name of the vendor.
	 * @param the_lname The last name of the vendor.
	 * @param the_address The address of the vendor.
	 * @param the_phonenumber The phone number of the vendor.
	 * @param the_email The email of the vendor.
	 * @param the_mem_paid The membership status of the vendor.
	 * @param the_UBI The UBI of the vendor.
	 * @param the_bus_name The business name of the vendor.
	 * @param the_stallNum The stall number of the vendor.
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public void createVendor(final String the_fname, final String the_lname, 
			final String the_address, final String the_phonenumber, final String the_email,
			final String the_mem_paid, final String the_UBI, final String the_bus_name,
			final String the_stallNum) throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("INSERT INTO vendor (vFirstName, vLastName," +
					"vAddress, vPhoneNumber, vEmail, vMem_paid, vUBI, vBusinessName, " +
					"sStallNumber) VALUES(?,?,?, ?,?,?, ?,?,?)");
			statement.setString(1, the_fname);
			statement.setString(2, the_lname);
			statement.setString(3, the_address);
			statement.setString(4, the_phonenumber);
			statement.setString(5, the_email);
			statement.setString(6, the_mem_paid);
			statement.setString(7, the_UBI);
			statement.setString(8, the_bus_name);
			statement.setString(9, the_stallNum);
			statement.executeUpdate();

		}
		finally {
			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * The venderQuery method is used to display the given vendor's transactions for 
	 * a given date range.
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException 
	 */
	public ArrayList<String[]> venderQuery(final String the_vID, final String the_vName, final String 
			the_startDate, final String the_endDate) throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT transaction.tID, sale.saleID, " +
					"sale.saleDate, sale.sPlants, sale.sProduce, sale.sProcessor, " +
					"sale.sPreparedFood, sale.sCrafter, sale.sReseller, " +
					"A.total AS 'Total_Sales',  sale.sNumOfStalls," +
					"Round((A.total * market.mMarketFee + sale.sNumOfStalls * market.mStallFee), 2) " +
					"as 'Market Fee', transaction.tPaid " +
					"FROM farmersmarketdb.market, farmersmarketdb.sale, " +
					"farmersmarketdb.transaction, farmersmarketdb.vendor," +
					"(SELECT (sale.sPlants + sale.sProduce + sale.sProcessor + " +
					"sale.sPreparedFood + sale.sCrafter + sale.sReseller) as 'total', saleID " +
					"FROM farmersmarketdb.sale) as A " +
					"WHERE (transaction.vendorID = ? OR vendor.vBusinessName = ?) and " +
					"sale.saleDate >= ? and sale.saleDate <= ? AND transaction.saleID = sale.saleID " +
					"AND transaction.vendorID = vendor.vID and transaction.saleID = a.saleID " +
					"and market.mID = transaction.marketID ORDER BY sale.saleDate");
			statement.setString(1, the_vID);
			statement.setString(2, the_vName);
			statement.setString(3, the_startDate);
			statement.setString(4, the_endDate);
			rs = statement.executeQuery();
			return RStoArray(rs);
		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}


	/**
	 * The vendorCount method queries the total number of vendors that had transactions
	 * for a given date range.
	 * @param the_startDate The start date of the query.
	 * @param the_endDate The end date of the query.
	 * @return An arraylist of String[] that contain the values.
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<String[]> venderCount(final String the_startDate, final String the_endDate) 
			throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT sale.saleDate, count(transaction.vendorID)" +
					"AS 'Vendor Count' FROM transaction, sale " +
					"WHERE transaction.saleID = sale.saleID AND sale.saleDate >= ? " +
					"AND sale.saleDate <= ? GROUP BY sale.saleDate");
			statement.setString(1, the_startDate);
			statement.setString(2, the_endDate);
			rs = statement.executeQuery();
			return RStoArray(rs);
		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}


	/**
	 * The marketTotalsByDate method returns the marketTotals for a given date range sorted
	 * by date and then vendor. 
	 * 
	 * @param the_startDate
	 * @param the_endDate
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<String[]> marketTotalsByDate(final String the_startDate, final String the_endDate) 
			throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT transaction.vendorID, vendor.vBusinessName, sale.saleDate, sum(A.Market_Fee)" +
					"FROM farmersmarketdb.transaction, farmersmarketdb.sale, farmersmarketdb.market, farmersmarketdb.vendor, " +
					"(SELECT transaction.vendorID as vID, sale.saleDate as sDate, " +
					"ROUND((((sale.sPlants + sale.sProduce + sale.sProcessor + sale.sPreparedFood + sale.sCrafter + sale.sReseller) * market.mMarketFee) "+
					"+ (sale.sNumOfStalls * market.mStallFee)), 2) AS Market_Fee " +
					"FROM farmersmarketdb.transaction, farmersmarketdb.sale, farmersmarketdb.market, farmersmarketdb.vendor " +
					"WHERE sale.saleDate >= ? and sale.saleDate <= ? " +
					"AND transaction.saleID = sale.saleID and transaction.vendorID = vendor.vID and transaction.marketID = market.mID) as A " +
					"WHERE sale.saleDate >= ? and sale.saleDate <= ? " +
					"AND transaction.saleID = sale.saleID and transaction.vendorID = vendor.vID " +
					"and transaction.marketID = market.mID and A.vID = transaction.vendorID and A.sDate = sale.saleDate " +
					"GROUP BY sale.saleDate, transaction.vendorID WITH ROLLUP");
			statement.setString(1, the_startDate);
			statement.setString(2, the_endDate);
			statement.setString(3, the_startDate);
			statement.setString(4, the_endDate);
			System.out.println(statement.toString());
			rs = statement.executeQuery();
			return RStoArray(rs);
		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * The marketTotalsByVendor method returns the marketTotals for a given date range sorted
	 * by vendor and then date 
	 * 
	 * @param the_startDate
	 * @param the_endDate
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<String[]> marketTotalsByVendor(final String the_startDate, final String the_endDate) 
			throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT transaction.vendorID, vendor.vBusinessName, sale.saleDate, sum(A.Market_Fee)" +
					"FROM farmersmarketdb.transaction, farmersmarketdb.sale, farmersmarketdb.market, farmersmarketdb.vendor, " +
					"(SELECT transaction.vendorID as vID, sale.saleDate as sDate, " +
					"ROUND((((sale.sPlants + sale.sProduce + sale.sProcessor + sale.sPreparedFood + sale.sCrafter + sale.sReseller) * market.mMarketFee) "+
					"+ (sale.sNumOfStalls * market.mStallFee)), 2) AS Market_Fee " +
					"FROM farmersmarketdb.transaction, farmersmarketdb.sale, farmersmarketdb.market, farmersmarketdb.vendor " +
					"WHERE sale.saleDate >= ? and sale.saleDate <= ? " +
					"AND transaction.saleID = sale.saleID and transaction.vendorID = vendor.vID and transaction.marketID = market.mID) as A " +
					"WHERE sale.saleDate >= ? and sale.saleDate <= ? " +
					"AND transaction.saleID = sale.saleID and transaction.vendorID = vendor.vID " +
					"and transaction.marketID = market.mID and A.vID = transaction.vendorID and A.sDate = sale.saleDate " +
					"GROUP BY transaction.vendorID, sale.saleDate WITH ROLLUP");
			statement.setString(1, the_startDate);
			statement.setString(2, the_endDate);
			statement.setString(3, the_startDate);
			statement.setString(4, the_endDate);
			rs = statement.executeQuery();
			return RStoArray(rs);
		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * 
	 * @param the_startDate
	 * @param the_endDate
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<String[]> marketTotalsByType(final String the_startDate, final String the_endDate) 
			throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT  sum.saleDate, sum.plantSum, " +
					"sum.produceSum, sum.processorSum, sum.preparedSum, sum.crafterSum, " +
					"sum.resellSum, (sum.plantSum + sum.produceSum + sum.processorSum + " +
					"sum.preparedSum +  sum.crafterSum + sum.resellSum) as dailyTotal " +
					"FROM (SELECT sale.saleDate, sum(sale.sPlants) as plantSum, " +
					"sum(sale.sProduce) as produceSum, sum(sale.sProcessor) as ProcessorSum, " +
					"sum(Sale.sPreparedFood) as PreparedSum, sum(sale.sCrafter) as crafterSum," +
					" sum(sale.sReseller) as resellSum " +
					"FROM sale WHERE sale.saleDate >= ? and " +
					"sale.saleDate <= ? GROUP BY sale.saleDate) as sum");
			statement.setString(1, the_startDate);
			statement.setString(2, the_endDate);
			rs = statement.executeQuery();
			return RStoArray(rs);

		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 * Displays all of the transaction that are outstanding.
	 * @param the_startDate The start date of the query.
	 * @param the_endDate The end date of the query.
	 * @return The string representation of the query.
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<String[]> outstandingTransactions(final String the_startDate, final String the_endDate)
			throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT sale.saleDate," +
					"vendor.vBusinessName, transaction.vendorID " +
					"FROM farmersmarketdb.sale, farmersmarketdb.transaction, farmersmarketdb.vendor " +
					"WHERE sale.saleID = transaction.saleID and transaction.vendorID = vendor.vID " +
					"AND sale.saleDate >= ? AND sale.saleDate <= ? AND transaction.tPaid = 'NO' " +
					"ORDER BY sale.saleDate ");
			statement.setString(1,  the_startDate);
			statement.setString(2,  the_endDate);

			rs = statement.executeQuery();

			return RStoArray(rs);
		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}


	/**
	 * A list of all vendors in the database.
	 * @return The string representation of all of the vendors in the database.
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public ArrayList<String[]> listOfVendors() throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT vendor.vID, vBusinessName, " +
					"vFirstName, vLastName, vAddress, vPhoneNumber, vUBI " +
					"FROM farmersmarketdb.vendor");
			rs = statement.executeQuery();
			return RStoArray(rs);
		}
		finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}

	/**
	 *  Queries a list of all of the market IDs in the market table.
	 * 
	 * @return An array of stings representing the market IDs.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public String[] getMarketIDs() throws ClassNotFoundException, SQLException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		String ret = "";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT market.mID FROM market");
			rs = statement.executeQuery();
			rs.next();
			ret += rs.getString(1);
			while(rs.next()){
				ret += ":" + rs.getString(1);
			}
			return ret.split(":");
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	} // end getMarketIDs

	/**
	 * Queries the market fee of a given market.
	 * 
	 * @param The_market The market whose fee will be returned.
	 * @return A string representing the market fee.  If no market exists an empty string
	 * will be returned.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public String getMarketFee(String the_market) throws ClassNotFoundException, SQLException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		String ret = "";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT market.mMarketFee FROM market " +
					"WHERE market.mID = ?");
			statement.setString(1, the_market);
			rs = statement.executeQuery();
			if(rs.next()) {
				ret = rs.getString(1); 
			}
			return ret;

		} finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	} // getMarketFee

	/**
	 * Sets the market and stall fee for the given marketID.
	 * @param the_mFee The market fee.
	 * @param the_sFee The stall fee.
	 * @param the_marketID The market ID to set the fees on.
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	public void setMarketStallFee(String the_mFee, String the_sFee, String the_marketID) throws SQLException, ClassNotFoundException {
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);

			statement = conn.prepareStatement("UPDATE market SET mMarketFee=?, " +
					"mStallFee=? WHERE mID=?");
			statement.setString(1, the_mFee);
			statement.setString(2, the_sFee);
			statement.setString(3, the_marketID);
			statement.executeUpdate();
		}
		finally {
			if (statement != null) {statement.close();}
			if (conn != null) {conn.close();}
		}
	} //end setMarketStallFee

	/**
	 * Gets all of the vendor IDs that exist in the vendor table.
	 * @return An arraylist containing the string representations of the vendor IDs.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public ArrayList<String> getVIDs() throws ClassNotFoundException, SQLException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		ArrayList<String> ret = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT vendor.vID FROM vendor");
			rs = statement.executeQuery();
			while(rs.next()) {
				ret.add(rs.getString(1));
			}
			return ret;		
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}// end getVIDs

	/**
	 * Queries all of the distinct dates in the sale table.
	 * @return An array list with the string representation of the dates.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public ArrayList<String> getSaleDates() throws ClassNotFoundException, SQLException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		ArrayList<String> ret = new ArrayList<String>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT DISTINCT sale.saleDate FROM sale");
			rs = statement.executeQuery();
			while(rs.next()) {
				ret.add(rs.getString(1));
			}
			return ret;		
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}// end getSaleDates

	/**
	 * Queries the stall fee of a given market.
	 * 
	 * @param the_market The market whose stall fee will be returned.
	 * @return A string representation of the stall fee.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public String getStallFee(String the_market) throws ClassNotFoundException, SQLException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		String ret = "";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT market.mStallFee from market " +
					"WHERE market.mID = ?");
			statement.setString(1, the_market);
			rs = statement.executeQuery();
			rs.next();
			ret = rs.getString(1);
			return ret;		
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}// end getStallFee


	/**
	 * Returns the payment status of a given transaction.
	 * @param the_vendorID The vendor to check.
	 * @param the_date The date of the sale.
	 * @return The payment status of the transaction.
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public String paidStatus(final String the_vendorID, final String the_date) throws ClassNotFoundException, SQLException {

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT transaction.tPaid " +
					"FROM farmersmarketdb.transaction, farmersmarketdb.sale " +
					"WHERE transaction.saleID = sale.saleID AND transaction.vendorID = ? " +
					"AND sale.saleDate = ?");
			statement.setString(1, the_vendorID);
			statement.setString(2, the_date);
			rs = statement.executeQuery();
			rs.next();
			return rs.getString(1);


		} finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}// end paidStatus

	/**
	 * Queries a specific transaction.
	 * @param the_tID
	 * @return The results of a specific transaction
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public String[] getVendorDailySales(final String the_tID) 
			throws ClassNotFoundException, SQLException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		String[] ret = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("Select transaction.vendorID, sale.sPlants, sale.sProduce," +
					"sale.sProcessor, sale.sPreparedFood, sale.sCrafter, sale.sReseller," +
					"transaction.tPaid, sale.sNumOfStalls, transaction.marketID " +
					"FROM farmersmarketdb.transaction, farmersmarketdb.sale " +
					"WHERE transaction.tID = ? AND transaction.saleID = sale.saleID");
			statement.setString(1, the_tID);
			rs = statement.executeQuery();
			ResultSetMetaData meta = rs.getMetaData();
			int colCnt = meta.getColumnCount();
			ret = new String[colCnt];
			rs.next();
			for (int i = 1; i <= colCnt; i++) {
				ret[i-1] = rs.getString(i);
			}
			return ret;
		} finally {
			if (rs != null) {
				rs.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}
	}// end getVendorDailySales


	public void updateVendorDailySales(final String the_vID, 
			final String the_plants, final String the_produce, final String the_processor,
			final String the_prep_food, final String the_crafter,
			final String the_reseller, final String the_paid, final String the_date, 
			final String the_stalls, final String the_marketID) 
					throws ClassNotFoundException, SQLException {
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("UPDATE farmersmarketdb.sale, " +
					"farmersmarketdb.transaction SET sale.sPlants = ?," +
					"sale.sProduce = ?, sale.sProcessor = ?, sale.sPreparedFood=?," +
					"sale.sCrafter=?, sale.sReseller=?, transaction.tPaid = ?, " +
					"sale.sNumOfStalls=?, transaction.marketID = ? " +
					"WHERE transaction.saleID = sale.saleID AND sale.saleDate = ? " +
					"AND transaction.vendorID = ?");
			statement.setString(1, the_plants);
			statement.setString(2,the_produce);
			statement.setString(3, the_processor);
			statement.setString(4,  the_prep_food);
			statement.setString(5, the_crafter);
			statement.setString(6, the_reseller);
			statement.setString(7, the_paid);
			statement.setString(8, the_stalls);
			statement.setString(9, the_marketID);
			statement.setString(10, the_date);
			statement.setString(11, the_vID);
			statement.executeUpdate();
		} finally {

			if (statement != null) {
				statement.close();
			}

			if (conn != null) {
				conn.close();
			}
		}


	}// end updateVendorDailySales

	/**
	 * Returns the earliest date from sales
	 * @return The earliest date
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public String getEarliestDate() throws ClassNotFoundException, SQLException {
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://" + dbServerName + "/" + database +
					"?" + "user=" + userName + "&password=" + pwd);
			statement = conn.prepareStatement("SELECT min(sale.saleDate) " +
					"FROM farmersmarketdb.sale");
			rs = statement.executeQuery();
			rs.next();
			return rs.getString(1);
		} finally {
			if (rs != null) { rs.close(); }
			if (statement != null) { statement.close(); }
			if (conn != null) { conn.close(); }
		}
	}

	//**************************************************************************************
	//*************** PRIVATE HELPER METHODS ***********************************************	
	//**************************************************************************************

	/**
	 * Converts a result set to a single string.
	 * @param the_rs The result set to convert to a string
	 * @return A string representation of the result set.
	 * @throws SQLException
	 */
	private String RStoString(final ResultSet the_rs) throws SQLException {
		StringBuilder sb = new StringBuilder();
		ResultSetMetaData meta = the_rs.getMetaData();
		int colCnt = meta.getColumnCount();
		String temp;
		int[] widths = new int[colCnt];
		//adds the column headers to sb
		int spaces;
		for (int i = 1; i <= colCnt; i++) {
			temp = "";
			spaces = 0;
			sb.append(meta.getColumnName(i));
			if(meta.getColumnName(i).length() > meta.getColumnDisplaySize(i)) {
				spaces = 1;
				widths[i-1] = meta.getColumnName(i).length() + 1; 
			} else {
				spaces = meta.getColumnDisplaySize(i) - meta.getColumnName(i).length() + 1;
				widths[i-1] = meta.getColumnDisplaySize(i) + 1;
			}
			for (int j = 0; j < spaces; j++) {

				temp += " ";
			}
			sb.append(temp);
		}
		sb.append("\r\n");
		// adds the rows to sb
		while(the_rs.next()){
			for(int i = 1; i <= colCnt; i++) {
				temp = "";
				sb.append(the_rs.getString(i));
				if(the_rs.getString(i) != null) {
					spaces = widths[i-1] - the_rs.getString(i).length();
				} else {
					spaces = widths[i-1] - 4
							;
				}

				for (int j = 1; j <= spaces; j++) {
					temp += " ";
				}
				sb.append(temp);
			}
			sb.append("\r\n");	
		}
		return sb.toString();
	} // end RStoString

	/**
	 * Converts a result set to an ArrayList of string arrays.
	 * @param the_rs The result set to be converted
	 * @return The array list of string arrays.
	 * @throws SQLException
	 */
	private ArrayList<String[]> RStoArray(final ResultSet the_rs) throws SQLException {
		ResultSetMetaData meta = the_rs.getMetaData();
		int colCnt = meta.getColumnCount();
		ArrayList<String[]> ret = new ArrayList<String[]>();
		String[] temp;
		while(the_rs.next()) {
			temp = new String[colCnt];
			for(int i = 1; i <= colCnt; i++) {
				temp[i -1] = the_rs.getString(i);
			}
			ret.add(temp);
		}

		return ret;


	}//end RStoArray



}// end Db class
